package com.hsgene.common.util.excle;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.springframework.web.multipart.MultipartFile;

import java.awt.*;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;

/**
 * 导出excel
 *
 * @author wxf
 * @date 2018/11/9 10:39
 **/
public class ExportExcelUtils {
    /**
     * Log
     */
    private static Logger LOGGER = LogManager.getLogger(ExportExcelUtils.class);

    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 导出Excel
     *
     * @param excelData 参数对象
     * @param out
     */
    public static void exportExcel(ExcelData excelData, OutputStream out) {
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = excelData.getSheetName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, excelData);
            wb.write(out);
        } catch (Exception e) {
            LOGGER.error("export excel is error...", e);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (Exception e) {
                LOGGER.error("close outputStream is error...", e);
            }
        }
    }

    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
        Map<String, String> cloumMap = data.getTitles();
        int rowIndex = writeTitlesToExcel(wb, sheet, cloumMap);
        Object o = data.getData();
        if (o != null) {
            writeRowsToExcel(wb, sheet, o, rowIndex, cloumMap);
        }
        autoSizeColumns(sheet, data.getTitles().size() + 1);
    }

    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, Map<String, String> titles) {
        int rowIndex = 0;
        XSSFFont titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setColor(IndexedColors.BLACK.getIndex());
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        Row titleRow = sheet.createRow(rowIndex);
        // titleRow.setHeightInPoints(25);
        int colIndex = 0;
        for (String key : titles.values()) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(key);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }

    private static void writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, Object rows, int rowIndex, Map<String, String>
            cloumMap) {
        XSSFFont dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setColor(IndexedColors.BLACK.getIndex());
        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        List<Object> values = (List<Object>) rows;
        for (Object cellData : values) {
            Row dataRow = sheet.createRow(rowIndex);
            //添加列
            addcolumnData(dataStyle, cellData, dataRow, cloumMap);
            rowIndex++;
        }
    }

    private static void addcolumnData(XSSFCellStyle dataStyle, Object cellData, Row dataRow, Map<String, String>
            cloumMap) {
        try {
            Class cls = cellData.getClass();
            int colIndex = 0;
            for (String fieldName : cloumMap.keySet()) {
                Field f = cls.getDeclaredField(fieldName);
                f.setAccessible(true);
                Object value = f.get(cellData);
                Cell cell = dataRow.createCell(colIndex);
                cell.setCellStyle(dataStyle);
                if (value == null) {
                    colIndex++;
                    continue;
                }
                //时间格式转换
                if (f.getType().toString().equals("class java.util.Date") && value != null) {
                    cell.setCellValue(sdf.format(value));
                } else {
                    cell.setCellValue(String.valueOf(value));
                }
                colIndex++;
            }
        } catch (Exception e) {
            LOGGER.error("export excel is error...", e);
        }
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = sheet.getColumnWidth(i) + 100;
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

    /**
     * 导入excel数据
     *
     * @param uploadFile 上传文件
     * @param cloumMap   excel表头模版名称(key-value形式：orderNo-申请单编号)
     * @param conField   判断的字段
     * @return
     */
    public static ExcelImportBean importExcel(MultipartFile uploadFile, Map<String, String> cloumMap,
                                              String conField) {
        Map<String, Object> tempMap = new LinkedHashMap<>();
        tempMap.putAll(cloumMap);
        ExcelImportBean importBean = new ExcelImportBean();
        try (InputStream is = uploadFile.getInputStream()) {
            Workbook wb = new XSSFWorkbook(is);
            Sheet sheet = wb.getSheetAt(0);
            //获取表头
            Row firstRow = sheet.getRow(0);
            //获取列数
            int cellNumber = firstRow.getPhysicalNumberOfCells();
            for (int i = 0; i < cellNumber; i++) {
                Cell cell = firstRow.getCell(i);
                String headName = cell.getStringCellValue();
                //校验表头
                if (!tempMap.containsValue(headName)) {
                    importBean.setData("上传文件表头名称：\"" + headName + "\"不存在于模版文件表头");
                    return importBean;
                }
                //替换cloumMap中的value为表格列index
                for (String key : tempMap.keySet()) {
                    if (tempMap.get(key).equals(headName)) {
                        tempMap.replace(key, i);
                    }
                }
            }
            //解析数据
            Object dataList = getAllDateToList(tempMap, conField, sheet);
            importBean.setData(dataList);
        } catch (Exception e) {
            importBean.setData("上传文件信息解析错误：" + e.getMessage());
            LOGGER.error("analysis excel file info is error...", e);
        }
        return importBean;
    }

    private static Object getAllDateToList(Map<String, Object> cloumMap, String conField, Sheet
            sheet) {
        int temp = 1;
        try {
            List<Map<Object, Object>> dataList = new ArrayList<>();
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                temp = i + 1;
                Row row = sheet.getRow(i);
                //判断特定字段的列是否为null，若为null，跳过
                int index = (int) cloumMap.get(conField);
                Cell cell = row.getCell(index);
                if (cell == null || "".equals(cell) || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    continue;
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                //校验数据类型
                Double.valueOf(cell.getStringCellValue());
                //获取列值
                Map<Object, Object> cellMap = getCellValueToMap(cloumMap, row);
                dataList.add(cellMap);
            }
            return dataList;
        } catch (Exception e) {
            String error = e.getMessage();
            if (error == null) {
                error = "数据格式异常";
            }
            LOGGER.error("data analysis is error...", e);
            return "导入文件第" + temp + "行存在数据类型错误，错误信息：" + error;
        }
    }

    private static Map<Object, Object> getCellValueToMap(Map<String, Object> cloumMap, Row row) {
        Map<Object, Object> cellMap = new HashMap<>();
        for (String key : cloumMap.keySet()) {
            Object o = cloumMap.get(key);
            if (!(o instanceof Integer)) {
                continue;
            }
            Cell cell = row.getCell((int) o);
            if (cell == null) {
                cellMap.put(key, null);
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String cellValue = cell.getStringCellValue();
                cellMap.put(key, cellValue);
            }
        }
        return cellMap;
    }
}
